﻿using KINLO.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KINLO.Data.Reporitory
{
    /// <summary>
    /// 罐压力表操作
    /// </summary>
    public class PressureVesselReporitory
    {
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["WeightDB"].ToString();
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(WeightReporitory));

        /// <summary>
        /// 根据时间批量查询
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<PressureVesselModel> GetPressureVesses(DateTime startTime, DateTime endTime)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();

                    var query = @"
                        SELECT Id, TimeStamp, Name, Type, PressureValue
                        FROM PressureVesse
                        WHERE TimeStamp >= @StartTime AND TimeStamp <= @EndTime
                    ";

                    var parameters = new SqlParameter[]
                    {
                        new SqlParameter("@StartTime", SqlDbType.DateTime) { Value = startTime },
                        new SqlParameter("@EndTime", SqlDbType.DateTime) { Value = endTime }
                    };

                    var result = new List<PressureVesselModel>();

                    using (var cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddRange(parameters);

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var model = new PressureVesselModel
                                {
                                    Id = (Guid)reader["Id"],
                                    TimeStamp = (DateTime)reader["TimeStamp"],
                                    Name = reader["Name"].ToString(),
                                    Type = reader["Type"].ToString(),
                                    PressureValue = (double)reader["PressureValue"]
                                };

                                result.Add(model);
                            }
                        }
                    }

                    return result;
                }
            }
            catch (Exception ex)
            {
                log.Error($"class: PressureVesselRepository, method: GetPressureVesses, 发生了错误: {ex}");
                return null;
            }
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="pressureVessels"></param>
        /// <returns></returns>
        public bool BulkInsertPressureVessels(List<PressureVesselModel> pressureVessels)
        {
            SqlTransaction transaction = null;

            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();

                    // 开始事务
                    transaction = conn.BeginTransaction();

                    // 创建一个DataTable来存储PressureVessel数据
                    DataTable dataTable = new DataTable();
                    dataTable.Columns.Add("Id", typeof(Guid));
                    dataTable.Columns.Add("TimeStamp", typeof(DateTime));
                    dataTable.Columns.Add("Name", typeof(string));
                    dataTable.Columns.Add("Type", typeof(string));
                    dataTable.Columns.Add("PressureValue", typeof(double));

                    // 将PressureVessel数据填充到DataTable
                    foreach (var vessel in pressureVessels)
                    {
                        dataTable.Rows.Add(
                            vessel.Id,
                            vessel.TimeStamp,
                            vessel.Name,
                            vessel.Type,
                            vessel.PressureValue
                        );
                    }

                    // 使用SqlBulkCopy批量插入数据
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.DestinationTableName = "PressureVesse"; // 指定目标表名
                        bulkCopy.BatchSize = pressureVessels.Count;
                        bulkCopy.BulkCopyTimeout = 60; // 超时时间（秒）

                        // 设置列映射
                        bulkCopy.ColumnMappings.Add("Id", "Id");
                        bulkCopy.ColumnMappings.Add("TimeStamp", "TimeStamp");
                        bulkCopy.ColumnMappings.Add("Name", "Name");
                        bulkCopy.ColumnMappings.Add("Type", "Type");
                        bulkCopy.ColumnMappings.Add("PressureValue", "PressureValue");

                        // 执行批量插入
                        bulkCopy.WriteToServer(dataTable);

                        // 提交事务
                        transaction.Commit();
                    }

                    return true;
                }
            }
            catch (Exception ex)
            {
                log.Error($"class: PressureVesselRepository, method: BulkInsertPressureVessels, 发生了错误: {ex}");

                // 回滚事务
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                return false;
            }
        }

        //public bool BulkInsertPressureVessels(List<PressureVesselModel> pressureVessels)
        //{
        //    try
        //    {
        //        using (var conn = new SqlConnection(ConnectionString))
        //        {
        //            conn.Open();

        //            // 创建一个DataTable来存储PressureVessel数据
        //            DataTable dataTable = new DataTable();
        //            dataTable.Columns.Add("Id", typeof(Guid));
        //            dataTable.Columns.Add("TimeStamp", typeof(DateTime));
        //            dataTable.Columns.Add("Name", typeof(string));
        //            dataTable.Columns.Add("Type", typeof(string));
        //            dataTable.Columns.Add("PressureValue", typeof(double));

        //            // 将PressureVessel数据填充到DataTable
        //            foreach (var vessel in pressureVessels)
        //            {
        //                dataTable.Rows.Add(
        //                    vessel.Id,
        //                    vessel.TimeStamp,
        //                    vessel.Name,
        //                    vessel.Type,
        //                    vessel.PressureValue
        //                );
        //            }

        //            // 使用SqlBulkCopy批量插入数据
        //            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        //            {
        //                bulkCopy.DestinationTableName = "PressureVesse"; // 指定目标表名
        //                bulkCopy.BatchSize = pressureVessels.Count;
        //                bulkCopy.BulkCopyTimeout = 60; // 超时时间（秒）

        //                // 设置列映射
        //                bulkCopy.ColumnMappings.Add("Id", "Id");
        //                bulkCopy.ColumnMappings.Add("TimeStamp", "TimeStamp");
        //                bulkCopy.ColumnMappings.Add("Name", "Name");
        //                bulkCopy.ColumnMappings.Add("Type", "Type");
        //                bulkCopy.ColumnMappings.Add("PressureValue", "PressureValue");

        //                // 执行批量插入
        //                bulkCopy.WriteToServer(dataTable);
        //            }

        //            return true;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        log.Error($"class: PressureVesselRepository, method: BulkInsertPressureVessels, 发生了错误: {ex}");
        //        return false;
        //    }
        //}


    }
}
